import pyodbc
from bs4 import BeautifulSoup
import datetime
import sys
import cv2
import requests
import os

this_number = 2
#~~~~~~SQL_Parameters~~~~~~#
if this_number == 2:
    sql_driver = '{ODBC driver 17 for SQL Server}'
    server = '192.168.85.51,1789' 
    database = 'Test0620'
    trusted_connection = 'no'
    uid = 'sa'  
    pwd = 'App1234'
    port = '1433'
    table = 'Test0620.dbo.ET_InvestNews'
    table_tag = 'Test0620.dbo.ET_InvestTags'
    table_relation_0 = 'Test0620.dbo.ET_InvestNewsToEnterprise'
    table_relation_1 = 'Test0620.dbo.ET_InvestRelationEnterprise'
    table_img = 'Test0620.dbo.ET_InvestImg'
elif this_number == 0:
    sql_driver = '{ODBC driver 17 for SQL Server}'
    server = 'Yu-GODS-LAPTOP'
    database= 'Project'
    trusted_connection = 'yes'
    uid = ''
    pwd = ''
    table = 'Project.dbo.ET_InvestNews'
    table_tag = 'Project.dbo.ET_InvestTags'
    table_relation_0 = 'Project.dbo.ET_InvestNewsToEnterprise'
    table_relation_1 = 'Project.dbo.ET_InvestRelationEnterprise'
    table_img = 'Project.dbo.ET_InvestImg'
    port = ""
elif this_number == 3:
    executable_path = r"C:\Users\ETwebadmin03\Desktop\InvestNews\geckodriver\geckodriver"
    firefox_profile = r"C:\Users\ETwebadmin03\AppData\Roaming\Mozilla\Firefox\Profiles\nv1wnnvi.default-release"
    sql_driver = '{ODBC driver 17 for SQL Server}'
    server = '192.168.85.51,1789' 
    database = 'ET_dbForEN'
    trusted_connection = 'no'
    uid = 'sa'  
    pwd = 'App1234'
    port = '1433'
    table = 'ET_dbForEN.dbo.ET_InvestNews'
    table_tag = 'ET_dbForEN.dbo.ET_InvestTags'
    table_relation_0 = 'ET_dbForEN.dbo.ET_InvestNewsToEnterprise'
    table_relation_1 = 'ET_dbForEN.dbo.ET_InvestRelationEnterprise'
    table_img = 'ET_dbForEN.dbo.ET_InvestImg'
#~~~~~~Connect_to_SQL~~~~~~#
connect_input = "driver=" + sql_driver + ";"
connect_input = connect_input + "Server=" + server + ";"
connect_input = connect_input + "Database=" + database + ";"
connect_input = connect_input + "Trusted_Connection=" + trusted_connection + ";" 
if port != "":
    connect_input = connect_input + "PORT=" + port + ";"
if uid != '' and pwd != '':
    connect_input = connect_input + "UID=" + uid + ";"
    connect_input = connect_input + "PWD=" + pwd
conn = pyodbc.connect(connect_input)
cursor = conn.cursor()
#~~~~~~Get_Contents~~~~~~#
current_time = datetime.datetime.now()
year = current_time.year
month = current_time.month
day = current_time.day
hour = current_time.hour
time_diff = datetime.timedelta(hours=1)
start_time = current_time-time_diff
start_time = datetime.datetime.strftime(start_time, "%Y-%m-%d %H:%M")
order_0 = "select ID,Title,Contents from " + table + " where Flag = \'1\'  and CreateDate > \'" + str(start_time) + "\'"
cursor.execute(order_0)
pair_list = list(cursor.fetchall())
#~~~~~~Parsering~~~~~~#
for elem in pair_list:
    news_id = elem[0]
    title = elem[1]
    contents = elem[2]
    soup = BeautifulSoup(contents, 'html.parser')
    img_list = soup.find_all('img')
    i = 1
    text_check = ""
    with open('edit_record.txt', 'a+', encoding= 'utf-8') as f:
        current_time = datetime.datetime.now()
        current_time = str(current_time)
        f.write(current_time)
        f.write('\n')
    order_1 =  "select NewsID,Url from " + table_img + " where NewsID = \'" + news_id + "\'"
    cursor.execute(order_1)
    existing_img_list = []
    existing_img_list = list(cursor.fetchall()) 
    with open('edit_record.txt', 'a+', encoding= 'utf-8') as f:
        f.write(order_1)
        f.write('ID:' + news_id + '\nExisting_img: ')
        for existing_img in existing_img_list:
            f.write(str(existing_img) + '\n')
        f.write('$$$$$$$$$$$$$$$$$$$$$$$$\n')
    for img in img_list:
        img_outerHTML = str(img).replace(r"'/", "").replace(r"'", "")
        try:
            print(news_id, title, str(img['src']).replace("\\", "").replace("\"", ""))
            img = str(img['src']).replace("\\", "").replace("\"", "")
        except:
            continue
        file_name = "temp_img.png"
        r = requests.get(img, allow_redirects = True)
        with open(file_name, 'wb') as f:
            f.write(r.content)
            f.close()
        image = cv2.imread('temp_img.png')
        qrCodeDetector = cv2.QRCodeDetector()
        try:
            decodedText, points, _ = qrCodeDetector.detectAndDecode(image)
        except:
            continue
        if points is not None and decodedText.replace(" ", "") != "":
            with open('edit_record.txt', 'a+', encoding= 'utf-8') as f:
                print(img, "含有二维码， 即将跳过！")
                f.write(img + "含有二维码: " + str(decodedText) + "已跳过\n")
                contents_correct =  contents.replace(img_outerHTML, "").replace("\'", r"\"")
                order_3 = "update " + table + " set Contents = N\'" + contents_correct + "\' where ID = \'" + news_id + "\'"
                cursor.execute(order_3)
                cursor.commit() 
            continue
        else:
            pass
        para_numb = 0
        for existing_img in existing_img_list:
            existing_img_url = existing_img[1]
            existing_img_NewsID = existing_img_NewsID[0]
            if news_id == existing_img_NewsID:
                print("已插入过此文章, 即将跳过！")
                para_numb = 1
                break
            if img in str(existing_img_url):
                print("已插入过此图片, 即将跳过！")
                para_numb = 1
                break
        if para_numb == 1:
            continue
        elif para_numb == 0:
            pass
        if img in text_check:
            print("已插入，即将跳过！")
            continue
        with open('edit_record.txt', 'a+', encoding= 'utf-8') as f:
            f.write(img)
            f.write('\n')
        print(img)
        text_check = text_check + img + "\n"
        order_2 = "insert into " + table_img + " (NewsID, Url, [Order]) values (\'" + news_id + "', N'" + img + "', '" + str(i) + "')"
        cursor.execute(order_2)
        cursor.commit()
        i += 1
    with open('edit_record.txt', 'a+', encoding= 'utf-8') as f:
        f.write('Text Check:')
        f.write(text_check)
        f.write('\n')
        f.close()
print("已插入此小时所有图片where NewsFlag = 1")
#~~~~~~Stock_Filter~~~~~~#
#~~~~~~Get_Contents~~~~~~#
current_time = datetime.datetime.now()
year = current_time.year
month = current_time.month
day = current_time.day
hour = current_time.hour
time_diff = datetime.timedelta(hours=1)
start_time = current_time-time_diff
start_time = datetime.datetime.strftime(start_time, "%Y-%m-%d %H:%M")
order_0 = "select ID,Title,Contents from " + table + " where Status = \'1\' and CreateDate > \'" + str(start_time) + "\'"
cursor.execute(order_0)
pair_list = list(cursor.fetchall())
keyword_list = ['股票', '证券', 'A股', '股市', '券', '涨停']
keyword_count = 0
#~~~~~~Parsering~~~~~~#
for elem in pair_list:
    news_id = elem[0]
    title = elem[1]
    contents = elem[2]
    soup = BeautifulSoup(contents, 'html.parser')
    text = soup.text
    keyword_count = 0
    for keyword in keyword_list:
        count = len(re.findall(keyword, text))
        print(title, keyword, count)
        keyword_count = keyword_count + count
    print(keyword_count)
    if keyword_count >= 3:
        order_1 = "update " + table + " set Status = \'0\' where ID = \'" + news_id + "\'"
        cursor.execute(order_1)
        cursor.commit()
        print(title, "出现相关字符", keyword_count, "次。已实行软删除！")
        with open('record.txt', 'a+', encoding='utf-8') as f:
            f.write(title + '  ' + news_id + " 已实行软删除！" + str(datetime.datetime.now()) + '\n')
            f.close()
print("已软删除所有此小时非相关新闻")
#~~~~~~Randomize_News~~~~~~~~~~~~~~#
from random import choices
#~~~~~~Select_Articles_within_Current_Hour~~~~~~#
current_time = datetime.datetime.now()
year = current_time.year
month = current_time.month
day = current_time.day
hour = current_time.hour
time_diff = datetime.timedelta(hours=3)
start_time = current_time-time_diff
start_time = datetime.datetime.strftime(start_time, "%Y-%m-%d %H:%M")
end_time = datetime.datetime(year=year, month=month, day=day, hour=hour+1, minute=0)
order_0 = "select ID, Source, Title from " + table + " where CreateDate > \'" + str(start_time) + "\' and CreateDate < \'" + str(end_time) + "\'"
##############sourceID_choice = random.choice(sourceID_list)
cursor.execute(order_0)
raw_list = cursor.fetchall()
source_list = []

for elem in raw_list:
    source = elem[1]
    source_list.insert(len(source_list), source)

raw_source_list = source_list
source_list = list(set(source_list))
source_total = len(source_list)
source_numb_list = list(range(0, source_total))
print("正在整理", str(len(raw_list)), "条新闻顺序. 共", str(len(source_list)), "类新闻来源.")


a = 0
for elem in source_list:
    p_string = "p_" + str(a)
    b = 0
    for elem_raw in raw_source_list:
        if elem_raw == elem:
            b = b + 1
    vars()[p_string] = b
    a += 1


base_numb = 0
for i in range(0, len(source_list), 1):
    p_string = "p_" + str(i)
    base_numb = base_numb + vars()[p_string]

pr_list = []
for i in range(0, len(source_list), 1):
    p_string = "p_" + str(i)
    pr_string = "pr_" + str(i)
    vars()[pr_string] = vars()[p_string]/base_numb
    pr_list.insert(len(pr_list), vars()[pr_string])

print(source_list)
print(pr_list)

    
    
class nameSet:
    def __init__(self, the_id, source, check_numb, title, img_numb):
        self.the_id = the_id
        self.source = source
        self.check_numb = check_numb
        self.title = title
        self.img_numb = img_numb

raw_list_0 = []
raw_list_1 = []
for elem in raw_list:
    the_id = elem[0]
    source = elem[1]
    title = elem[2]
    check_numb = 0
    order_1 = 'select Url from ' + table_img + ' where NewsID = \'' + the_id + "\'"
    cursor.execute(order_1)
    img_list = list(cursor.fetchall())
    img_numb = len(img_list)
    if img_numb != 0:
        print(img_list, img_numb)
    if img_numb == 0:
        the_set = nameSet(the_id=the_id, source=source, check_numb=check_numb, title=title, img_numb=img_numb)
        raw_list_0.insert(len(raw_list_0), the_set)
    elif img_numb > 0:
        the_set = nameSet(the_id=the_id, source=source, check_numb=check_numb, title=title, img_numb=img_numb)
        raw_list_1.insert(len(raw_list_1), the_set)
######################################################
print(len(raw_list_0), len(raw_list_1))
j = 1
final_list = []
for i in range(1, len(raw_list_0)+1, 1):
    para_numb = 0
    while True:
        source_numb_choice = choices(source_numb_list, pr_list)[0]
        source_choice = source_list[source_numb_choice]
        k = 0
        for elem in raw_list_0:
            check_numb = elem.check_numb
            if check_numb == 1:
                k += 1
                continue
            source = elem.source
            the_id = elem.the_id
            title = elem.title
            img_numb = elem.img_numb
            if source == source_choice:
                para_numb = 1
                raw_list_0[k].check_numb = 1
                break
            k += 1
        if para_numb == 1:
            break
        elif para_numb == 0:
            continue
    pair_list = [the_id, str(j), source, title, img_numb]
    print(pair_list)
    j += 1
    final_list.insert(len(final_list), pair_list)

order_1 = "select Max(bak11) from " + table + " where CreateDate < \'" + str(start_time) + "\'"
cursor.execute(order_1)
max_numb = cursor.fetchall()[0][0]
if max_numb == None:
    max_numb = 0
elif max_numb != None:
    max_numb = int(max_numb)
for elem in final_list:
    the_id = elem[0]
    bak11 = int(elem[1]) + max_numb
    print(the_id, elem[3], elem[2], bak11)
    order_2 = "update " + table + " set bak11=\'" + str(bak11) + "\' where ID=\'" + the_id + "\'"
    cursor.execute(order_2)
    cursor.commit()
####################################################################
j = 1
final_list = []
for i in range(1, len(raw_list_1)+1, 1):
    para_numb = 0
    while True:
        source_numb_choice = choices(source_numb_list, pr_list)[0]
        source_choice = source_list[source_numb_choice]
        k = 0
        for elem in raw_list_1:
            check_numb = elem.check_numb
            if check_numb == 1:
                k += 1
                continue
            source = elem.source
            the_id = elem.the_id
            title = elem.title
            img_numb = elem.img_numb
            if source == source_choice:
                para_numb = 1
                raw_list_1[k].check_numb = 1
                break
            k += 1
        if para_numb == 1:
            break
        elif para_numb == 0:
            continue
    pair_list = [the_id, str(j), source, title, img_numb]
    print(pair_list)
    j += 1
    final_list.insert(len(final_list), pair_list)

order_1 = "select Max(bak11) from " + table + " where CreateDate < \'" + str(start_time) + "\'" #and bak11 != None"
cursor.execute(order_1)
max_numb = cursor.fetchall()[0][0]
if max_numb == None:
    max_numb = 0
elif max_numb != None:
    max_numb = int(max_numb)
for elem in final_list:
    the_id = elem[0]
    bak11 = int(elem[1]) + max_numb
    print(the_id, elem[3], elem[2], bak11)
    order_2 = "update " + table + " set bak11=\'" + str(bak11) + "\' where ID=\'" + the_id + "\'"
    cursor.execute(order_2)
    cursor.commit()
# except Exception as e:
#     print("There is an exception")
#     print(e)
#     exc_type, exc_obj, exc_tb = sys.exc_info()
#     fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
#     print(exc_type, fname, exc_tb.tb_lineno)
#     input()